﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Xml;

namespace WSNSX07
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {        
        [WebMethod(EnableSession = true)]
        public XmlDocument loginNSX(string username, string password)
        {
            XmlDocument doc = new XmlDocument();
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

            SqlCommand cmd = new SqlCommand("SELECT * FROM Customer WHERE username = @username", cn);
            cmd.Parameters.Add("@username", SqlDbType.VarChar);
            cmd.Parameters["@username"].Value = username;
            cn.Open();
            // DataSet ds = new DataSet(cmd, cn); 
            SqlDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            if (rdr.HasRows == false) return doc;
            if (password == rdr["password"].ToString())
            {
                XmlElement author = doc.CreateElement("key");
                author.InnerText = HttpContext.Current.Session.SessionID;
                doc.AppendChild(author);
                Session["key"] = HttpContext.Current.Session.SessionID;
                Session["user"] = username;
                Session["time"] = Convert.ToString(DateTime.Now.ToLocalTime());
                cn.Close();
                return doc;
            }
            else
            {
                XmlElement author = doc.CreateElement("key");
                author.InnerText = "false";
                doc.AppendChild(author);
                return doc;
            }
        }


        public int checkSession(string key, string username)
        {
            if (Session["key"].ToString() == key && Session["user"].ToString() == username)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        [WebMethod(EnableSession = true)]
        public XmlDocument getCategory(string key, string username)
        {
            XmlDocument doc = new XmlDocument();
            if (1 == 1)
            {
                SqlConnection cn = new SqlConnection();
                XmlElement root = doc.CreateElement("root");
                doc.AppendChild(root);
                cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

                SqlCommand cmd = new SqlCommand("SELECT * FROM Category ORDER BY name ASC", cn);
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows == false) return doc;
                //int idM = Convert.ToInt32(rdr["id"].ToString());
                //cn.Close();
                while (rdr.Read())
                {
                    XmlElement item = doc.CreateElement("item");
                    root.AppendChild(item);                   

                    XmlElement name = doc.CreateElement("name");
                    name.InnerText = rdr["name"].ToString();
                    item.AppendChild(name);
                    XmlElement id = doc.CreateElement("id");
                    id.InnerText = rdr["id"].ToString();
                    item.AppendChild(id);
                    XmlElement hard_soft = doc.CreateElement("hard_soft");
                    hard_soft.InnerText = rdr["hard_soft"].ToString();
                    item.AppendChild(hard_soft);
                }
                return doc;
            }
            else
            {
                return doc;
            }
        }

        [WebMethod(EnableSession = true)]
        public XmlDocument getProduct(string key, string username, int idCategory)
        {
            XmlDocument doc = new XmlDocument();
            if (1 == 1)
            {
                SqlConnection cn = new SqlConnection();
                XmlElement root = doc.CreateElement("root");
                doc.AppendChild(root);
                cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

                SqlCommand cmd = new SqlCommand("SELECT * FROM Product WHERE idCategory = @idCategory ORDER By name ASC", cn);
                cmd.Parameters.Add("@idCategory", SqlDbType.Int);
                cmd.Parameters["@idCategory"].Value = idCategory;
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows == false) return doc;
                //int idM = Convert.ToInt32(rdr["id"].ToString());
                //cn.Close();
                while (rdr.Read())
                {
                    XmlElement item = doc.CreateElement("item");
                    root.AppendChild(item);

                    XmlElement name = doc.CreateElement("name");
                    name.InnerText = rdr["name"].ToString();
                    item.AppendChild(name);
                    XmlElement id = doc.CreateElement("id");
                    id.InnerText = rdr["id"].ToString();
                    item.AppendChild(id);
                    XmlElement price = doc.CreateElement("price");
                    price.InnerText = rdr["price"].ToString();
                    item.AppendChild(price);
                    XmlElement imageUrl = doc.CreateElement("imageUrl");
                    imageUrl.InnerText = rdr["imageUrl"].ToString();
                    item.AppendChild(imageUrl);
                    XmlElement description = doc.CreateElement("description");
                    description.InnerText = rdr["description"].ToString();
                    item.AppendChild(description);
                }
                return doc;
            }
            else
            {
                return doc;
            }
        }

        [WebMethod(EnableSession = true)]
        public XmlDocument getProductByName(string key, string username, string keyword)
        {
            XmlDocument doc = new XmlDocument();
            if (1 == 1)
            {
                SqlConnection cn = new SqlConnection();
                XmlElement root = doc.CreateElement("root");
                doc.AppendChild(root);
                cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

                SqlCommand cmd = new SqlCommand("SELECT * FROM Product WHERE name like '%" + keyword + "%' ORDER By name ASC", cn);
                //cmd.Parameters.Add("@keyword", SqlDbType.NVarChar);
               // cmd.Parameters["@keyword"].Value = keyword;
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows == false) return doc;
                //int idM = Convert.ToInt32(rdr["id"].ToString());
                //cn.Close();
                while (rdr.Read())
                {
                    XmlElement item = doc.CreateElement("item");
                    root.AppendChild(item);

                    XmlElement name = doc.CreateElement("name");
                    name.InnerText = rdr["name"].ToString();
                    item.AppendChild(name);
                    XmlElement id = doc.CreateElement("id");
                    id.InnerText = rdr["id"].ToString();
                    item.AppendChild(id);
                    XmlElement price = doc.CreateElement("price");
                    price.InnerText = rdr["price"].ToString();
                    item.AppendChild(price);
                    XmlElement imageUrl = doc.CreateElement("imageUrl");
                    imageUrl.InnerText = rdr["imageUrl"].ToString();
                    item.AppendChild(imageUrl);
                    XmlElement description = doc.CreateElement("description");
                    description.InnerText = rdr["description"].ToString();
                    item.AppendChild(description);
                }
                return doc;
            }
            else
            {
                return doc;
            }
        }

        [WebMethod(EnableSession = true)]
        public XmlDocument getProductLastest(string key, string username)
        {
            XmlDocument doc = new XmlDocument();
            if (1 == 1)
            {
                SqlConnection cn = new SqlConnection();
                XmlElement root = doc.CreateElement("root");
                doc.AppendChild(root);
                cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

                SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Product ORDER By id DESC", cn);
                //cmd.Parameters.Add("@keyword", SqlDbType.NVarChar);
                // cmd.Parameters["@keyword"].Value = keyword;
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows == false) return doc;
                //int idM = Convert.ToInt32(rdr["id"].ToString());
                //cn.Close();
                while (rdr.Read())
                {
                    XmlElement item = doc.CreateElement("item");
                    root.AppendChild(item);

                    XmlElement name = doc.CreateElement("name");
                    name.InnerText = rdr["name"].ToString();
                    item.AppendChild(name);
                    XmlElement id = doc.CreateElement("id");
                    id.InnerText = rdr["id"].ToString();
                    item.AppendChild(id);
                    XmlElement price = doc.CreateElement("price");
                    price.InnerText = rdr["price"].ToString();
                    item.AppendChild(price);
                    XmlElement imageUrl = doc.CreateElement("imageUrl");
                    imageUrl.InnerText = rdr["imageUrl"].ToString();
                    item.AppendChild(imageUrl);
                    XmlElement description = doc.CreateElement("description");
                    description.InnerText = rdr["description"].ToString();
                    item.AppendChild(description);
                }
                return doc;
            }
            else
            {
                return doc;
            }
        }

        [WebMethod(EnableSession = true)]
        public int buy(string key, string username, float Iprice, int Inumber, int Ilongterm, int Iproduct, string IdateBegin, string IdateEnd, int Imin, string confirm)
        {
            XmlDocument doc = new XmlDocument();
            if (1 == 1)
            {
                SqlConnection cn = new SqlConnection();
                XmlElement root = doc.CreateElement("root");
                doc.AppendChild(root);
                cn.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();


                SqlCommand cmd = new SqlCommand("SELECT * FROM Customer WHERE username = @username", cn);
                cmd.Parameters.Add("@username", SqlDbType.NVarChar);
                cmd.Parameters["@username"].Value = username;
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                SqlDataReader rdr = cmd.ExecuteReader();
                rdr.Read();
                string customerid = "";
                if (rdr.HasRows == false) return 0;
                else
                {
                    customerid = rdr["id"].ToString();
                }
                cn.Close();

                cmd = new SqlCommand("INSERT INTO TransactionEC (idUsername, dateTime, price, number, longterm, idProduct, dateBegin, dateEnd, min, confirm) VALUES (@user, @datetime, @price, @number, @longterm, @product, @dateBegin, @dateEnd, @min, @confirm)", cn);
                cmd.Parameters.Add("@user", SqlDbType.Int);
                cmd.Parameters["@user"].Value = Convert.ToInt32(customerid);

                cmd.Parameters.Add("@datetime", SqlDbType.DateTime);
                cmd.Parameters["@datetime"].Value = DateTime.Now.Date;

                cmd.Parameters.Add("@price", SqlDbType.Int);
                cmd.Parameters["@price"].Value = Iprice;

                cmd.Parameters.Add("@number", SqlDbType.Float);
                cmd.Parameters["@number"].Value = Inumber;

                cmd.Parameters.Add("@longterm", SqlDbType.Int);
                cmd.Parameters["@longterm"].Value = Ilongterm;

                cmd.Parameters.Add("@product", SqlDbType.Int);
                cmd.Parameters["@product"].Value = Iproduct;

                cmd.Parameters.Add("@dateBegin", SqlDbType.Date);
                cmd.Parameters["@dateBegin"].Value = IdateBegin;

                cmd.Parameters.Add("@dateEnd", SqlDbType.Date);
                cmd.Parameters["@dateEnd"].Value = IdateEnd;

                cmd.Parameters.Add("@min", SqlDbType.Int);
                cmd.Parameters["@min"].Value = Imin;

                cmd.Parameters.Add("@confirm", SqlDbType.NVarChar);
                cmd.Parameters["@confirm"].Value = confirm; 
                
                cn.Open();
                // DataSet ds = new DataSet(cmd, cn); 
                int result = cmd.ExecuteNonQuery();
                cn.Close();
                return result;
            }
            else
            {
                return 0;
            }
        }
    }
}
